/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package alphas.a2.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * Class that is used by IClientAuth interface methods, to verify the existance
 * of a certain user in the database.
 * 
 * @author npassaro
 */
public class VerifyUser {

	private static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
	private String DB_URL; // database where the users table is stored
	private Boolean connectError = false;
	private ResultSet res = null; // SQL query result set pointer
	private Statement s = null; // SQL statement pointer

	/**
	 * 
	 * @param username
	 *            the user login name
	 * @param password
	 *            the user password
	 * @param type
	 *            type of user that is being verified. The user can be IT
	 *            manager, Order Operator and Shipping
	 * @return true if the username, password and type, inserted as parameters,
	 *         exist in a row of the users table on users database. Otherwise,
	 *         return false.
	 */
	protected boolean verify(String username, String password, String type,
			String host, int port) {
		Connection conn = null;
		try {
			// Register JDBC driver
			Class.forName(VerifyUser.JDBC_DRIVER);
			DB_URL = "jdbc:mysql://" + host + ":" + port + "/users";
			// Open a connection
			System.out.println("Connecting to a selected database...");
			conn = DriverManager.getConnection(DB_URL, "remote", "remote_pass");
			System.out.println("Connected database successfully...");
		} catch (SQLException se) {
			// Handle errors for JDBC
			System.out.println("[VerifyUser] Database connection error: "
					+ se.getMessage());
			se.printStackTrace();
			connectError = true;
		} catch (Exception e) {
			// Handle errors for Class.forName
			System.out.println("[VerifyUser] Database connection error: "
					+ e.getMessage());
			e.printStackTrace();
			connectError = true;
		}
		// If there is not a connection error, do the query
		if (!connectError) {
			try {
				s = conn.createStatement();
				// select a register of a user that has username, password and
				// usertype
				String selectString = "SELECT username, password, usertype FROM users WHERE NOT STRCMP(username,'"
						+ username
						+ "')"
						+ " AND NOT STRCMP(password,'"
						+ password
						+ "')"
						+ " AND NOT STRCMP(usertype,'"
						+ type
						+ "');";
				// Query to assess if the user with that password that is from
				// the type is authorized
				res = s.executeQuery(selectString);
				System.out.println(res.first());

				return res.first();

			} catch (Exception e) {
				System.out.println("[VerifyUser] Database query error: "
						+ e.getMessage());
			} finally {
				// finally block used to close resources
				try {
					if (conn != null) {
						conn.close();
					}
				} catch (SQLException se) {
				}// end finally try
			}// end try// end try-catch
		} // if connect check
		return false;
	}
}
